﻿using System.Linq;
using System.Text;
using System.Diagnostics;
using System.Runtime.Serialization;
using System.Collections;
using System.Data;
using System.ComponentModel;
using System.Data.OracleClient;
namespace INTEX.DataBase.Tools
{
    public partial class INTEXDBC
    {
        public DataRow Convert2GABEW(DataRow dwInput, string ITXDBVERSION, bool IsConvert = false)
        {
            DataSet SResult = new DataSet();
            SResult = QueryGABEW(dwInput, 0);
            DataRow dwReturn = null;
            if (SResult.Tables[0].Rows.Count > 0)
            {
                dwReturn = SResult.Tables[0].Rows[0];
            }
            else
            {
                dwReturn = SResult.Tables[0].NewRow();
            }
            switch (ITXDBVERSION)
            {
                case "YGR":
                default:
                    dwReturn["GABMANDANT"] = "00";
                    dwReturn["GABJJJJ"] = "2014";
                    dwReturn["GABBELEG"] = "504";
                    dwReturn["GABLFDNR"] = "176";
                    dwReturn["GABPARTIE"] = "0000035";
                    dwReturn["GABPARTIEKZ"] = "0";
                    dwReturn["GABSPSTA"] = "IB";
                    dwReturn["GABZENSUR"] = "1";
                    dwReturn["GABLO"] = "01";
                    dwReturn["GABLB"] = "CP";
                    dwReturn["GABLP"] = "A       ";
                    dwReturn["GABAUFBKZ"] = " ";
                    dwReturn["GABLBEH"] = "0000000281";
                    dwReturn["GABLBEHI"] = "0000000281";
                    dwReturn["GABART"] = "40021.NE.20.1       ";
                    dwReturn["GABVAR"] = "   ";
                    dwReturn["GABFARBE"] = "Y9059S    ";
                    dwReturn["GABDEZI"] = " ";
                    dwReturn["GABAUFTYP"] = " ";
                    dwReturn["GABAUFKRS"] = "  ";
                    dwReturn["GABAUFLFD"] = "000000";
                    dwReturn["GABAUFPOS"] = "000";
                    dwReturn["GABAFOLGE"] = "000";
                    dwReturn["GABTEILNR"] = "0000";
                    dwReturn["GABSA"] = "   ";
                    dwReturn["GABPOS"] = "00";
                    dwReturn["GABKSTKRS"] = "  ";
                    dwReturn["GABKST"] = "0000";
                    dwReturn["GABKOSTART"] = "      ";
                    dwReturn["GABBS"] = "43";
                    dwReturn["GABWERTDAT"] = "20141020";
                    dwReturn["GABKG"] = "20000";
                    dwReturn["GABKA"] = "1";
                    dwReturn["GABSP"] = "0";
                    dwReturn["GABWERT"] = "0";
                    dwReturn["GABWEKORR"] = "  ";
                    dwReturn["GABBUCHDAT"] = "20141020";
                    dwReturn["GABBUCHZEIT"] = "11544313";
                    dwReturn["GABBUCHBEN"] = "KONGCH  ";
                    dwReturn["GABFC"] = "GB15    ";
                    dwReturn["GABKONDAT"] = "0";
                    dwReturn["GABMATABR"] = " ";
                    dwReturn["GABSTORKZ"] = " ";
                    dwReturn["GABINVBLG"] = "0";
                    dwReturn["GABDISPOKZ"] = " ";
                    dwReturn["GABAPOS"] = "0000";
                    dwReturn["GABACTION"] = "  ";
                    dwReturn["GABIKOND"] = "0000";
                    dwReturn["GABCARRIER"] = "        ";
                    dwReturn["GABCARRPOS"] = "000";
                    dwReturn["GABMNRKRS"] = "  ";
                    dwReturn["GABMLFDNR"] = "0000";
                    dwReturn["GABMPOS"] = "00";
                    dwReturn["GABSCHICHT"] = "0";
                    dwReturn["GABFILLER"] = "                                       ";
                    break;
            }
            if (IsConvert)
            {
                foreach (DataColumn cm in dwInput.Table.Columns)
                {
                    if (dwInput[cm.ColumnName].ToString() == "")
                        continue;
                    if (dwReturn.Table.Columns.Contains(cm.ColumnName))
                    {
                        dwReturn[cm.ColumnName] = dwInput[cm.ColumnName];
                    }
                }
            }
            return dwReturn;
        }
        public void SaveGABEW(DataRow dwData, string ITXDBVERSION = "50.24")
        {
            DataSet setResult = new DataSet();
            DataTable tbLocal = null;
            DataRow dwLocal = null;
            bool flgExist = false;
            setResult = QueryGABEW(dwData, 2);
            if (setResult.Tables.Count > 0)
            {
                tbLocal = setResult.Tables[0];
                if (tbLocal.Rows.Count > 0)
                {
                    flgExist = true;
                    dwLocal = tbLocal.Rows[0];
                }
                else
                {
                    dwLocal = tbLocal.NewRow();
                }
            }
            MapGABEW(ref dwLocal, dwData);
            switch (ITXDBVERSION)
            {
                case "YGR":
                default:
                    if (!flgExist)
                    {
                        InsertGABEW(dwLocal, 1);
                    }
                    UpdateGABEW(dwLocal, 1);
                    break;
            }
        }
        public DataSet QueryGABEW(DataRow dwData, int option = 1)
        {
            DataSet setReturn = new DataSet();
            DataSet Result = new DataSet();
            DataTable tbLocal = null;
            OracleCommand or1;
            // GSCMANDANT GSCART GSCVAR GSCFARBE


            or1 = OracleDB.CreateCommand();
            switch (option)
            {
                case 0:
                    or1.CommandText = "SELECT * FROM GABEW WHERE  ROWNUM<2 ";
                    break;
                case 1:
                    or1.CommandText = "SELECT GABBUCHDAT,GABART,GABVAR,GABFARBE,GPBSPPART,GPBZWPART,SUM(GABKG) AS GABKG,0 AS PRICE,0 AS SURCHAGE,0 AS VATVALUE,0 AS NETVALUE FROM ("
                        + "SELECT GABBELEG,GABBUCHDAT,GABLBEHI,GABART,GABVAR,GABFARBE,GPBSPPART,GPBZWPART,GABAUFKRS,GABAUFLFD,GABAUFPOS,GABKG*0.001 AS GABKG"
                        + "  FROM INTEX.GABEW JOIN GPBST ON GABMANDANT=GPBMANDANT AND GPBPARTIE= GABPARTIE"
                        + " WHERE  GABMANDANT=:GABMANDANT AND GABBUCHDAT>=:GABBUCHDAT AND GABLO=:GABLO AND GABLB=:GABLB AND GABBS IN (" + dwData["C20"].ToString() + ") ) X"
                        + " GROUP BY GABBUCHDAT,GABART,GABVAR,GABFARBE,GPBSPPART,GPBZWPART";
                    or1.Parameters.Add("GABMANDANT", OracleType.NVarChar).Value = dwData["VKPMANDANT"].ToString();
                    or1.Parameters.Add("GABBUCHDAT", OracleType.NVarChar).Value = dwData["DSTART"].ToString();
                    or1.Parameters.Add("GABLO", OracleType.NVarChar).Value = dwData["C2"].ToString();
                    or1.Parameters.Add("GABLB", OracleType.NVarChar).Value = dwData["C3"].ToString().Trim();
                    break;
                case 2:
                    or1.CommandText = "SELECT * FROM GABEW  "
                        + " WHERE  GABMANDANT=:GABMANDANT AND GABJJJJ=:GABJJJJ AND GABBELEG=:GABBELEG AND GABLFDNR=:GABLFDNR";
                    or1.Parameters.Add("GABMANDANT", OracleType.NVarChar).Value = dwData["GABMANDANT"];
                    or1.Parameters.Add("GABJJJJ", OracleType.NVarChar).Value = dwData["GABJJJJ"];
                    or1.Parameters.Add("GABBELEG", OracleType.NVarChar).Value = dwData["GABBELEG"];
                    or1.Parameters.Add("GABLFDNR", OracleType.NVarChar).Value = dwData["GABLFDNR"];
                    break;
                case 3:
                    or1.CommandText = "SELECT * FROM GABEW  "
                        + " WHERE  GABMANDANT=:GABMANDANT AND GABAUFKRS=:GABAUFKRS AND GABAUFLFD=:GABAUFLFD AND GABAUFPOS=:GABAUFPOS AND GABLO=:GABLO AND GABLB=:GABLB AND GABLP=:GABLP AND GABLBEHI=:GABLBEHI AND GABBS=:GABBS";
                    or1.Parameters.Add("GABMANDANT", OracleType.NVarChar).Value = dwData["GABMANDANT"];
                    or1.Parameters.Add("GABAUFKRS", OracleType.NVarChar).Value = dwData["GABAUFKRS"];
                    or1.Parameters.Add("GABAUFLFD", OracleType.NVarChar).Value = dwData["GABAUFLFD"];
                    or1.Parameters.Add("GABAUFPOS", OracleType.NVarChar).Value = dwData["GABAUFPOS"];
                    or1.Parameters.Add("GABLO", OracleType.NVarChar).Value = dwData["GABLO"];
                    or1.Parameters.Add("GABLB", OracleType.NVarChar).Value = dwData["GABLB"];
                    or1.Parameters.Add("GABLP", OracleType.NVarChar).Value = dwData["GABLP"];
                    or1.Parameters.Add("GABLBEHI", OracleType.NVarChar).Value = dwData["GABLBEHI"];
                    or1.Parameters.Add("GABBS", OracleType.NVarChar).Value = dwData["GABBS"]; 
                    break;
                default:
                    break;
            }
            Result = Connect2Proxy(or1);
            if (Result.Tables.Count > 0)
            {
                tbLocal = Result.Tables[0];
                tbLocal.TableName = "GABEW";
            }
            setReturn.Merge(Result);
            return setReturn;
        }
       
        public void MapGABEW(ref DataRow dwLocal, DataRow dwData)
        {
            BlindMap(ref dwLocal, dwData);
        }
        public void UpdateGABEW(DataRow dwData, int option = 1)
        {
            OracleCommand or1;
            or1 = OracleDB.CreateCommand();
            switch (option)
            {
                case 1:
                    or1.CommandText = "UPDATE GABEW SET "
                    + "GABMANDANT=:GABMANDANT,"
                    + "GABJJJJ=:GABJJJJ,"
                    + "GABBELEG=:GABBELEG,"
                    + "GABLFDNR=:GABLFDNR,"
                    + "GABPARTIE=:GABPARTIE,"
                    + "GABPARTIEKZ=:GABPARTIEKZ,"
                    + "GABSPSTA=:GABSPSTA,"
                    + "GABZENSUR=:GABZENSUR,"
                    + "GABLO=:GABLO,"
                    + "GABLB=:GABLB,"
                    + "GABLP=:GABLP,"
                    + "GABAUFBKZ=:GABAUFBKZ,"
                    + "GABLBEH=:GABLBEH,"
                    + "GABLBEHI=:GABLBEHI,"
                    + "GABART=:GABART,"
                    + "GABVAR=:GABVAR,"
                    + "GABFARBE=:GABFARBE,"
                    + "GABDEZI=:GABDEZI,"
                    + "GABAUFTYP=:GABAUFTYP,"
                    + "GABAUFKRS=:GABAUFKRS,"
                    + "GABAUFLFD=:GABAUFLFD,"
                    + "GABAUFPOS=:GABAUFPOS,"
                    + "GABAFOLGE=:GABAFOLGE,"
                    + "GABTEILNR=:GABTEILNR,"
                    + "GABSA=:GABSA,"
                    + "GABPOS=:GABPOS,"
                    + "GABKSTKRS=:GABKSTKRS,"
                    + "GABKST=:GABKST,"
                    + "GABKOSTART=:GABKOSTART,"
                    + "GABBS=:GABBS,"
                    + "GABWERTDAT=:GABWERTDAT,"
                    + "GABKG=:GABKG,"
                    + "GABKA=:GABKA,"
                    + "GABSP=:GABSP,"
                    + "GABWERT=:GABWERT,"
                    + "GABWEKORR=:GABWEKORR,"
                    + "GABBUCHDAT=:GABBUCHDAT,"
                    + "GABBUCHZEIT=:GABBUCHZEIT,"
                    + "GABBUCHBEN=:GABBUCHBEN,"
                    + "GABFC=:GABFC,"
                    + "GABKONDAT=:GABKONDAT,"
                    + "GABMATABR=:GABMATABR,"
                    + "GABSTORKZ=:GABSTORKZ,"
                    + "GABINVBLG=:GABINVBLG,"
                    + "GABDISPOKZ=:GABDISPOKZ,"
                    + "GABAPOS=:GABAPOS,"
                    + "GABACTION=:GABACTION,"
                    + "GABIKOND=:GABIKOND,"
                    + "GABCARRIER=:GABCARRIER,"
                    + "GABCARRPOS=:GABCARRPOS,"
                    + "GABMNRKRS=:GABMNRKRS,"
                    + "GABMLFDNR=:GABMLFDNR,"
                    + "GABMPOS=:GABMPOS,"
                    + "GABSCHICHT=:GABSCHICHT,"
                    + "GABFILLER=:GABFILLER "
                    + " WHERE  "
                    + "  GABMANDANT=:GABMANDANT AND GABJJJJ=:GABJJJJ  AND GABBELEG=:GABBELEG AND GABLFDNR=:GABLFDNR";


                    or1.Parameters.Add("GABMANDANT", OracleType.NVarChar).Value = dwData["GABMANDANT"].ToString();
                    or1.Parameters.Add("GABJJJJ", OracleType.NVarChar).Value = dwData["GABJJJJ"].ToString();
                    or1.Parameters.Add("GABBELEG", OracleType.NVarChar).Value = dwData["GABBELEG"].ToString();
                    or1.Parameters.Add("GABLFDNR", OracleType.NVarChar).Value = dwData["GABLFDNR"].ToString();
                    or1.Parameters.Add("GABPARTIE", OracleType.NVarChar).Value = dwData["GABPARTIE"].ToString();
                    or1.Parameters.Add("GABPARTIEKZ", OracleType.NVarChar).Value = dwData["GABPARTIEKZ"].ToString();
                    or1.Parameters.Add("GABSPSTA", OracleType.NVarChar).Value = dwData["GABSPSTA"].ToString();
                    or1.Parameters.Add("GABZENSUR", OracleType.NVarChar).Value = dwData["GABZENSUR"].ToString();
                    or1.Parameters.Add("GABLO", OracleType.NVarChar).Value = dwData["GABLO"].ToString();
                    or1.Parameters.Add("GABLB", OracleType.NVarChar).Value = dwData["GABLB"].ToString();
                    or1.Parameters.Add("GABLP", OracleType.NVarChar).Value = dwData["GABLP"].ToString();
                    or1.Parameters.Add("GABAUFBKZ", OracleType.NVarChar).Value = dwData["GABAUFBKZ"].ToString();
                    or1.Parameters.Add("GABLBEH", OracleType.NVarChar).Value = dwData["GABLBEH"].ToString();
                    or1.Parameters.Add("GABLBEHI", OracleType.NVarChar).Value = dwData["GABLBEHI"].ToString();
                    or1.Parameters.Add("GABART", OracleType.NVarChar).Value = dwData["GABART"].ToString();
                    or1.Parameters.Add("GABVAR", OracleType.NVarChar).Value = dwData["GABVAR"].ToString();
                    or1.Parameters.Add("GABFARBE", OracleType.NVarChar).Value = dwData["GABFARBE"].ToString();
                    or1.Parameters.Add("GABDEZI", OracleType.NVarChar).Value = dwData["GABDEZI"].ToString();
                    or1.Parameters.Add("GABAUFTYP", OracleType.NVarChar).Value = dwData["GABAUFTYP"].ToString();
                    or1.Parameters.Add("GABAUFKRS", OracleType.NVarChar).Value = dwData["GABAUFKRS"].ToString();
                    or1.Parameters.Add("GABAUFLFD", OracleType.NVarChar).Value = dwData["GABAUFLFD"].ToString();
                    or1.Parameters.Add("GABAUFPOS", OracleType.NVarChar).Value = dwData["GABAUFPOS"].ToString();
                    or1.Parameters.Add("GABAFOLGE", OracleType.NVarChar).Value = dwData["GABAFOLGE"].ToString();
                    or1.Parameters.Add("GABTEILNR", OracleType.NVarChar).Value = dwData["GABTEILNR"].ToString();
                    or1.Parameters.Add("GABSA", OracleType.NVarChar).Value = dwData["GABSA"].ToString();
                    or1.Parameters.Add("GABPOS", OracleType.NVarChar).Value = dwData["GABPOS"].ToString();
                    or1.Parameters.Add("GABKSTKRS", OracleType.NVarChar).Value = dwData["GABKSTKRS"].ToString();
                    or1.Parameters.Add("GABKST", OracleType.NVarChar).Value = dwData["GABKST"].ToString();
                    or1.Parameters.Add("GABKOSTART", OracleType.NVarChar).Value = dwData["GABKOSTART"].ToString();
                    or1.Parameters.Add("GABBS", OracleType.NVarChar).Value = dwData["GABBS"].ToString();
                    or1.Parameters.Add("GABWERTDAT", OracleType.NVarChar).Value = dwData["GABWERTDAT"].ToString();
                    or1.Parameters.Add("GABKG", OracleType.NVarChar).Value = dwData["GABKG"].ToString();
                    or1.Parameters.Add("GABKA", OracleType.NVarChar).Value = dwData["GABKA"].ToString();
                    or1.Parameters.Add("GABSP", OracleType.NVarChar).Value = dwData["GABSP"].ToString();
                    or1.Parameters.Add("GABWERT", OracleType.NVarChar).Value = dwData["GABWERT"].ToString();
                    or1.Parameters.Add("GABWEKORR", OracleType.NVarChar).Value = dwData["GABWEKORR"].ToString();
                    or1.Parameters.Add("GABBUCHDAT", OracleType.NVarChar).Value = dwData["GABBUCHDAT"].ToString();
                    or1.Parameters.Add("GABBUCHZEIT", OracleType.NVarChar).Value = dwData["GABBUCHZEIT"].ToString();
                    or1.Parameters.Add("GABBUCHBEN", OracleType.NVarChar).Value = dwData["GABBUCHBEN"].ToString();
                    or1.Parameters.Add("GABFC", OracleType.NVarChar).Value = dwData["GABFC"].ToString();
                    or1.Parameters.Add("GABKONDAT", OracleType.NVarChar).Value = dwData["GABKONDAT"].ToString();
                    or1.Parameters.Add("GABMATABR", OracleType.NVarChar).Value = dwData["GABMATABR"].ToString();
                    or1.Parameters.Add("GABSTORKZ", OracleType.NVarChar).Value = dwData["GABSTORKZ"].ToString();
                    or1.Parameters.Add("GABINVBLG", OracleType.NVarChar).Value = dwData["GABINVBLG"].ToString();
                    or1.Parameters.Add("GABDISPOKZ", OracleType.NVarChar).Value = dwData["GABDISPOKZ"].ToString();
                    or1.Parameters.Add("GABAPOS", OracleType.NVarChar).Value = dwData["GABAPOS"].ToString();
                    or1.Parameters.Add("GABACTION", OracleType.NVarChar).Value = dwData["GABACTION"].ToString();
                    or1.Parameters.Add("GABIKOND", OracleType.NVarChar).Value = dwData["GABIKOND"].ToString();
                    or1.Parameters.Add("GABCARRIER", OracleType.NVarChar).Value = dwData["GABCARRIER"].ToString();
                    or1.Parameters.Add("GABCARRPOS", OracleType.NVarChar).Value = dwData["GABCARRPOS"].ToString();
                    or1.Parameters.Add("GABMNRKRS", OracleType.NVarChar).Value = dwData["GABMNRKRS"].ToString();
                    or1.Parameters.Add("GABMLFDNR", OracleType.NVarChar).Value = dwData["GABMLFDNR"].ToString();
                    or1.Parameters.Add("GABMPOS", OracleType.NVarChar).Value = dwData["GABMPOS"].ToString();
                    or1.Parameters.Add("GABSCHICHT", OracleType.NVarChar).Value = dwData["GABSCHICHT"].ToString();
                    or1.Parameters.Add("GABFILLER", OracleType.NVarChar).Value = dwData["GABFILLER"].ToString();
                    break;
                default:
                    break;
            }

            Connect2Proxy(or1);
        }
        public void InsertGABEW(DataRow dwData, int option = 1)
        {

            OracleCommand or1;

            or1 = OracleDB.CreateCommand();
            switch (option)
            {
                case 1:
                    or1.CommandText = "INSERT INTO GABEW(GABMANDANT, GABJJJJ, GABBELEG, GABLFDNR, GABPARTIE, GABPARTIEKZ, GABSPSTA, GABZENSUR, GABLO, GABLB, GABLP, GABAUFBKZ, GABLBEH, GABLBEHI, GABART, GABVAR, GABFARBE, GABDEZI, GABAUFTYP, GABAUFKRS, GABAUFLFD, GABAUFPOS, GABAFOLGE, GABTEILNR, GABSA, GABPOS, GABKSTKRS, GABKST, GABKOSTART, GABBS, GABWERTDAT, GABKG, GABKA, GABSP, GABWERT, GABWEKORR, GABBUCHDAT, GABBUCHZEIT, GABBUCHBEN, GABFC, GABKONDAT, GABMATABR, GABSTORKZ, GABINVBLG, GABDISPOKZ, GABAPOS, GABACTION, GABIKOND, GABCARRIER, GABCARRPOS, GABMNRKRS, GABMLFDNR, GABMPOS, GABSCHICHT, GABFILLER)"
                        + " VALUES (:GABMANDANT, :GABJJJJ, :GABBELEG, :GABLFDNR, '0000052', '0', 'IB', '1', '01', 'CP', 'A       ', ' ', '0000000325', '0000000325', '40021.NE.16.1       ', '   ', 'YB038-2   ', ' ', ' ', '  ', '000000', '000', '000', '0000', '   ', '00', '  ', '0000', '      ', '43', 20141020, 9000, 1, 0, 0, '  ', 20141020, 15443411, 'SUNL    ', 'GB15    ', 0, ' ', ' ', 0, ' ', '0000', '  ', '0000', '        ', '000', '  ', '0000', '00', '0', '                                       ')";
                    or1.Parameters.Add("GABMANDANT", OracleType.NVarChar).Value = dwData["GABMANDANT"];
                    or1.Parameters.Add("GABJJJJ", OracleType.NVarChar).Value = dwData["GABJJJJ"];
                    or1.Parameters.Add("GABBELEG", OracleType.NVarChar).Value = dwData["GABBELEG"];
                    or1.Parameters.Add("GABLFDNR", OracleType.NVarChar).Value = dwData["GABLFDNR"];
                    break;
                default:
                    break;
            }

            Connect2Proxy(or1);
        }
    }
}
